#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project    :icac2 
@File       :core.py
@Author     :wangfan13
@Date       :2021/5/28 9:50 
"""
import datetime
import random
import pymysql
from django.db import connection
from django.db.models import Q
from django.http import JsonResponse
from rest_framework.response import Response

from apps.case.get_fields import get_case_name, get_case_code, get_case_date, get_case_owner, getpage, getdate_rb, \
    getdate_cheat, get_rb_owner, get_cheat_owner, get_case_name_relationship, get_agent_rb, get_agent_cheat, \
    get_amount_rb, get_amount_cheat
from apps.case.models import Case
from apps.utils.mysql_utils import DBUtil, DBUtil_Dict
from apps.utils.response import response_result


def case_insert(request):
    # 判断用户是否登录或存在
    # auth_log().log_check(request)
    global settle_repetition_amount, data_cheat_amount
    try:
        data = request.params['data']
        exists = Case.objects.filter(Q(case_name=data.get('case_name')) | Q(case_code=data.get('case_code')))
        if len(exists) > 0:
            return JsonResponse({'ret': 2, 'res': '案件名或案件编号已存在！'})
        conn = connection
        cursor = conn.cursor()
        # 要加入的案件列表
        order_list = data.get('order_list', list())
        so_list = data.get('so_list', list())
        # # 需要加入的案件列表
        settle_repetition_amount = ((0,),)
        print(type(settle_repetition_amount))
        data_cheat_amount = ((0,),)
        print(type(data_cheat_amount))
        if len(order_list) > 0:
            settle_sql = "select if(sum(realrefund) is null,0,sum(realrefund)) as case_amount from RB_Settle_Check where ordercode in (%s)" % ','.join(
                ['%s'] * len(order_list))
            cursor.execute(settle_sql, order_list)
            settle_repetition_amount = cursor.fetchall()

            for order in order_list:
                sql_or = "select * from case_relationship where markkey=\'%s\' and keytype=\'OR\'" % order
                cursor.execute(sql_or)
                or_res = cursor.fetchall()
                if len(or_res) == 0:
                    relationship_sql = "insert into case_relationship (casename,markkey,keytype) " \
                                       "values (\'%s\','%s','OR')" % (data.get('case_name'), order)
                else:
                    # relationship_sql = "update case_relationship set casename=\'%s\',markkey=%s,keytype='OR'" \
                    #                    " where markkey=\'%s\' and keytype=\'OR\'" % (
                    #                        data.get('case_name'), order, order)
                    return response_result.res_faile(msg='该信息已立案')
                cursor.execute(relationship_sql)
                conn.commit()
        if len(so_list) > 0:
            data_cheat_sql = "select if(sum(订单产品总价) is null,0,sum(订单产品总价)) as case_amount from datacheat_1 where 订单SO in (%s)" % ','.join(
                ['%s'] * len(so_list))
            cursor.execute(data_cheat_sql, so_list)
            data_cheat_amount = cursor.fetchall()
            print(type(data_cheat_amount))
            # 插入数据
            for so in so_list:
                sql_or = "select * from case_relationship where markkey=\'%s\' and keytype=\'DC\'" % so
                cursor.execute(sql_or)
                or_res = cursor.fetchall()
                if len(or_res) == 0:
                    relationship_sql = "insert into case_relationship (casename,markkey,keytype) " \
                                       "values (\'%s\','%s','DC')" % (data.get('case_name'), so)
                else:
                    # relationship_sql = "update case_relationship set casename=\'%s\',markkey=%s,keytype='DC'" \
                    #                    " where markkey=\'%s\' and keytype=\'DC\'" % (data.get('case_name'), so, so)
                    return response_result.res_faile(msg='该信息已立案')
                cursor.execute(relationship_sql)
                conn.commit()
        case_amount = float(settle_repetition_amount[0][0]) + float(data_cheat_amount[0][0])
        # 根据案件明细生成案件code
        # hash_md5 = hashlib.md5(str(data).encode("utf8"))
        # case_code = hash_md5.hexdigest()[5:] + random.choice('abcdefghijklmnopqrstuvwxyz')
        case_code = "AJBH" + datetime.datetime.strftime(datetime.datetime.now(), '%Y%m%d') + ''.join(
            random.sample('0123456789', 4))
        # 创建表,
        Case.objects.create(case_name=data.get('case_name'), case_code=case_code,
                            case_owner=data.get('policy_owner'),
                            case_owner_itcode=data.get('case_owner_itcode', ''),
                            case_amount=case_amount, data_source=data.get('data_source', ''),
                            case_remark=data.get('case_remark'))
        try:
            Case.objects.get(case_name=data.get('case_name'))
            return response_result.res_success()
        except Case.DoesNotExist:
            return response_result.res_faile(msg='案件新增失败！')
    except Exception as e:
        print(e)
        return response_result.res_faile(e, msg='案件新增失败！')


def case_query(request):
    dbs = DBUtil_Dict()
    global total
    total = 0
    try:
        if request.params.get('data', -1) == -1:
            # sql = 'select * from case_case'
            sql = 'select * from case_case order by case_establish_time desc'
            rs = dbs.get_all(sql)
            sql_total = 'select count(id) total from case_case'
            print(sql_total)
            res_total = dbs.get_all(sql_total)
            if res_total:
                total = res_total[0].get("total")
            return response_result.res_total_success(total, rs)
        else:
            data = request.params.get('data')
            sql = 'select * from case_case where 1=1 ' + get_case_name(data) + get_case_code(data) + get_case_owner(
                data) + get_case_date(data) + ' order by case_establish_time desc ' + getpage(data)
            print(sql)
            rs = dbs.get_all(sql)
            sql_total = 'select count(id) total from case_case where 1=1 ' + get_case_name(data) + get_case_code(
                data) + get_case_owner(data) + get_case_date(data)
            print(sql_total)
            res_total = dbs.get_all(sql_total)
            if res_total:
                total = res_total[0].get("total")
            return response_result.res_total_success(total, rs)
    except Exception as err:
        return response_result.res_faile(err)

    # if request.params.get('type', -1) == 'query':
    #     info = request.params['data']
    #     print(Q(case_owner=info.get('search_key', '!@#$')))
    #     print(Q(case_case_establish_time=info.get('case_time')))
    #     queryset = Case.objects.filter(
    #         Q(case_name__contains=info.get('case_name', '!@#$'))
    #         | Q(case_code__contains=info.get('case_code'))
    #         | Q(case_owner__contains=info.get('case_owner', '!@#$'))
    #         | Q(case_establish_time=info.get('case_time'))
    #         )
    #     output_list = []
    #     for query in queryset:
    #         casse_dict = model_to_dict(query)
    #         casse_dict['case_establish_time'] = query.case_establish_time.strftime('%Y-%m-%d %H:%M:%S')
    #         output_list.append(casse_dict)
    #     if len(output_list) != 0:
    #         return JsonResponse(output_list, safe=False)
    #     else:
    #         return JsonResponse({'ret': 1, 'res': '未找到符合条件的案件！'})
    # elif request.params.get('type', -1) == 'all':
    #     queryset = Case.objects.filter()
    #     output_list = []
    #     for query in queryset:
    #         casse_dict = model_to_dict(query)
    #         casse_dict['case_establish_time'] = query.case_establish_time.strftime('%Y-%m-%d %H:%M:%S')
    #         output_list.append(casse_dict)
    #     if len(output_list) != 0:
    #         return JsonResponse(output_list, safe=False)
    #     else:
    #         return JsonResponse({'ret': 1, 'res': '未找到符合条件的案件！'})


def case_delete(request):
    name_list = request.params['case_name']
    failed_lsit = []
    for name in name_list:
        case = None
        try:
            case = Case.objects.get(case_name=name)
        except Case.DoesNotExist:
            failed_lsit.append(name)
        if case:
            case.delete()
            sql = "delete from case_relationship where casename = \'%s\'" % name
            conn = connection
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
    if failed_lsit:
        return JsonResponse({'ret': 1, 'res': f'有case_code为 {failed_lsit} 的案件删除失败！'})
    return JsonResponse({'ret': 0, 'res': f'{len(name_list)}件案件删除成功！'})


def case_update(request):
    info = request.params['data']
    order_list = info.get('order_list')
    del_order_list = info.get('del_order_list')
    so_list = info.get('so_list')
    del_so_list = info.get('del_so_list')
    try:
        case = Case.objects.get(case_name=request.params['case_name'])
    except Case.DoesNotExist:
        return JsonResponse({'ret': 1, 'res': f'有ID为 {info.get("case_name")} 的案件已存在！'})
    if case:
        case.case_name = info.get('case_name', case.case_name)
        case.case_owner = info.get('case_owner', case.case_owner)
        case.case_owner_itcode = info.get('case_owner_itcode', case.case_owner_itcode)
        case.case_amount = info.get('case_amount', case.case_amount)
        case.data_source = info.get('data_source', case.data_source)
        case.case_remark = info.get('case_remark', case.case_remark)
        case.save()
        conn = connection
        cursor = conn.cursor()
        for order in order_list:
            sql_or = "select * from case_relationship where markkey=\'%s\' and keytype=\'OR\'" % order
            cursor.execute(sql_or)
            or_res = cursor.fetchall()
            if len(or_res) == 0:
                relationship_sql = "insert into case_relationship (casename,markkey,keytype) " \
                                   "values (\'%s\','%s','OR')" % (info.get('case_name'), order)
            else:
                relationship_sql = "update case_relationship set casename=\'%s\',markkey=%s,keytype='OR'" \
                                   " where markkey=\'%s\' and keytype=\'OR\'" % (info.get('case_name'), order, order)
            cursor.execute(relationship_sql)
            conn.commit()
        for so in so_list:
            sql_or = "select * from case_relationship where markkey=\'%s\' and keytype=\'DC\'" % so
            cursor.execute(sql_or)
            or_res = cursor.fetchall()
            if len(or_res) == 0:
                relationship_sql = "insert into case_relationship (casename,markkey,keytype) " \
                                   "values (\'%s\','%s','DC')" % (info.get('case_name'), so)
            else:
                relationship_sql = "update case_relationship set casename=\'%s\',markkey=%s,keytype='DC'" \
                                   " where markkey=\'%s\' and keytype=\'DC\'" % (info.get('case_name'), so, so)
            cursor.execute(relationship_sql)
            conn.commit()
        for o in del_order_list:
            sql = "delete from case_relationship where markkey = \'%s\' and keytype='OR'" % o
            cursor.execute(sql)
            conn.commit()
        for s in del_so_list:
            sql = "delete from case_relationship where markkey = \'%s\' and keytype='DC'" % s
            cursor.execute(sql)
            conn.commit()
        return JsonResponse({'ret': 0, 'res': 'group modify successfully！'})
    else:
        return JsonResponse({'ret': 1, 'res': f'有ID为 {request.params["pk"]} 的案件更新失败！'})


def query_policy_owner():
    try:
        dbs = DBUtil()
        sql_rb_query_policy_owner_itcode = "select distinct settleowner from RB_Settle_Check"
        sql_dc_query_policy_owner_itcode = "select distinct `政策解释人` from datacheat_1"

        rb_query_policy_owner_itcode = dbs.get_all(sql_rb_query_policy_owner_itcode)
        dc_query_policy_owner_itcode = dbs.get_all(sql_dc_query_policy_owner_itcode)

        res_set = set()
        res_list = []
        if len(rb_query_policy_owner_itcode) > 0:
            for rb in rb_query_policy_owner_itcode:
                print(rb)
                res_set.add(rb[0])
        if len(dc_query_policy_owner_itcode) > 0:
            for rb in dc_query_policy_owner_itcode:
                res_set.add(rb[0])
        if len(res_set) > 0:
            for rs in res_set:
                res_dir = {"name": rs}
                res_list.append(res_dir)
        return JsonResponse({'ret': 0, 'data': res_list, 'res': 'query successfully！'}, safe=False)
    except Exception as e:
        return JsonResponse({'ret': 1, 'res': e}, safe=False)


def query_case_details_by_policy_owner(params):
    try:
        dbs = DBUtil_Dict()
        data = params.get('data')
        total = 0
        sql_query_case_by_policy_owner = "select * from (select ordercode,settlecode,agent,reaptetimes,realrefund,shouldrefund,'' productmodel," \
                                         "'' orderproductotaltnum,'' orderproductprice,'' orderproducttotalprice,0 casemode,submitdate  FROM " \
                                         "case_relationship a left join RB_Settle_Check b on b.`ordercode`=cast(a.markkey as SIGNED) " \
                                         "where 1=1 " + get_rb_owner(data) + get_case_name_relationship(data) + \
                                         " union " \
                                         "select `订单SO` ordercode," \
                                         "`结算案编号` settlecode,`T1代理名称` agent,'' reaptetimes,'' realrefund,'' shouldrefund,'' productmodel," \
                                         "`产品数量` orderproductotaltnum,`产品单价` orderproductprice,`订单产品总价` orderproducttotalprice,1 casemode, `策划案提交时间` submitdate FROM" \
                                         " case_relationship a left join datacheat_1 b on b.`订单SO`=cast(a.markkey as SIGNED) where 1=1 " \
                                         + get_cheat_owner(data) + get_case_name_relationship(data) + ") a " + getpage(
            data)
        print(sql_query_case_by_policy_owner)
        sql_query_case_by_policy_owner_total = "select count(ordercode) total from ( select * from (select ordercode,settlecode,agent,reaptetimes,realrefund,shouldrefund,'' productmodel," \
                                               "'' orderproductotaltnum,'' orderproductprice,'' orderproducttotalprice,0 casemode  FROM " \
                                               "case_relationship a left join RB_Settle_Check b on b.`ordercode`=cast(a.markkey as SIGNED) " \
                                               "where 1=1 " + get_rb_owner(data) + get_case_name_relationship(data) + \
                                               " union " \
                                               "select `订单SO` ordercode," \
                                               "`结算案编号` settlecode,`T1代理名称` agent,'' reaptetimes,'' realrefund,'' shouldrefund,'' productmodel," \
                                               "`产品数量` orderproductotaltnum,`产品单价` orderproductprice,`订单产品总价` orderproducttotalprice,1 casemode FROM" \
                                               " case_relationship a left join datacheat_1 b on b.`订单SO`=cast(a.markkey as SIGNED) where 1=1 " \
                                               + get_cheat_owner(data) + get_case_name_relationship(data) + ") a ) bb"
        print(sql_query_case_by_policy_owner_total)
        rs = dbs.get_all(sql_query_case_by_policy_owner)
        res_total = dbs.get_all(sql_query_case_by_policy_owner_total)
        if res_total:
            total = res_total[0].get("total")
        return response_result.res_total_success(total, rs)
    except Exception as e:
        print(e)
        return JsonResponse({'ret': 1, 'res': e}, safe=False)


def query_not_case_info(params):
    try:
        dbs = DBUtil_Dict()
        data = params.get('data')
        total = 0
        sql_query_not_case_data = "select * from (select ordercode,settlecode,agent,reaptetimes,realrefund,shouldrefund,'' productmodel,'' orderproductotaltnum," \
                                  "'' orderproductprice,'' orderproducttotalprice,0 casemode,submitdate from RB_Settle_Check " \
                                  "where 1=1 " + get_rb_owner(data) + get_agent_rb(data) + get_amount_rb(
            data) + " and settleowner is not null" + getdate_rb(
            data) + " and ordercode not in (select markkey from case_relationship where keytype='OR')" \
                    " union all " \
                    "select `订单SO` ordercode,`结算案编号` settlecode,`T1代理名称` agent,'' reaptetimes,'' realrefund,'' shouldrefund,'' productmodel," \
                    "`产品数量` orderproductotaltnum,`产品单价` orderproductprice,`订单产品总价` orderproducttotalprice,1 casemode, `策划案提交时间` submitdate from datacheat_1 " \
                    "where 1=1 " + get_cheat_owner(data) + get_agent_cheat(data) + get_amount_cheat(
            data) + " and 政策解释人 is not null " + getdate_cheat(
            data) + "and `订单SO` not in (select markkey from case_relationship where keytype='DC')) a" + getpage(data)
        print(sql_query_not_case_data)
        sql_query_not_case_data_total = "select count(ordercode) total from ( select * from (select ordercode,settlecode,agent,reaptetimes,realrefund,shouldrefund,'' productmodel,'' orderproductotaltnum," \
                                        "'' orderproductprice,'' orderproducttotalprice,0 casemode from RB_Settle_Check " \
                                        "where 1=1 " + get_rb_owner(data) + get_agent_rb(data) + get_amount_rb(
            data) + " and settleowner is not null" + getdate_rb(
            data) + " and ordercode not in (select markkey from case_relationship where keytype='OR')" \
                    "  union all " \
                    "select `订单SO` ordercode,`结算案编号` settlecode,`T1代理名称` agent,'' reaptetimes,'' realrefund,'' shouldrefund,'' productmodel," \
                    "`产品数量` orderproductotaltnum,`产品单价` orderproductprice,`订单产品总价` orderproducttotalprice,1 casemode from datacheat_1 " \
                    "where 1=1 " + get_cheat_owner(data) + get_agent_cheat(data) + get_amount_cheat(
            data) + " and 政策解释人 is not null " + getdate_cheat(
            data) + "and `订单SO` not in (select markkey from case_relationship where keytype='DC')) a ) bb"
        print(sql_query_not_case_data_total)
        rs = dbs.get_all(sql_query_not_case_data)
        res_total = dbs.get_all(sql_query_not_case_data_total)
        if res_total:
            total = res_total[0].get("total")
        return response_result.res_total_success(total, rs)
    except Exception as e:
        return response_result.res_faile(e)


def query_case_name():
    try:
        sql = 'select case_name from case_case where 1=1 '
        dbs = DBUtil_Dict()
        rs = dbs.get_all(sql)
        return response_result.res_success(rs)
    except Exception as err:
        return response_result.res_faile(err)
